#include "../include/SqlServer.h"

SqlServer::SqlServer(muduo::net::EventLoop* loop, const muduo::net::InetAddress& listenAddr)
: loop_(loop), server_(loop, listenAddr, "SqlServer")
{
    server_.setConnectionCallback(std::bind(&SqlServer::onConnection, this, muduo::_1));
    server_.setMessageCallback(std::bind(&SqlServer::onMessage, this, muduo::_1, muduo::_2, muduo::_3));
}

void SqlServer::onConnection(const muduo::net::TcpConnectionPtr& conn)
{
   
}

void SqlServer::onMessage(const muduo::net::TcpConnectionPtr& conn, muduo::net::Buffer* buf, muduo::Timestamp time)
{
    while (buf->readableBytes() >= Header_Length) {
        // 查看长度字段
        int32_t be32 = buf->peekInt32();
        const int32_t len = muduo::net::sockets::networkToHost32(be32);
        if (len > 65536 || len < 0) {
            LOG_ERROR << "Invalid length " << len;
            conn->shutdown();
            break;
        }
        else if (buf->readableBytes() >= Header_Length + len) {
            buf->retrieve(Header_Length);
            std::string message(buf->retrieveAsString(len));
            parseMessage(conn, message);
        }
        else {
            break;
        }
    }
}

void SqlServer::parseMessage(const muduo::net::TcpConnectionPtr& conn, const std::string& message)
{
    Json::Reader reader;
    Json::Value root;

    if (reader.parse(message, root)) {
        std::string request = root["request"].asString();

        auto get_mac = [&]()-> std::string {
            std::string mac = root["mac"].asString();
            for (auto& m : mac) {   // 把 '-' 替换成 '_'
                if (m == '-') m = '_';
            }
            return mac;
        };

        if (request == "backup") {  // 执行数据备份任务
            std::string mac = get_mac();
            std::string tableName = root["user"].asString() + "_" + mac + "_data";
            std::string date = root["date"].asString();
            float temp = root["temp"].asFloat();
            float humi = root["humi"].asFloat();
            createUserDataTable(tableName);
            thread_pool::globalInstance()->submit(std::bind(&SqlServer::insertUserData, this, tableName, date, temp, humi));
        }
        else if (request == "create") { // 执行建表任务
            // 设备数据表名为 "用户名_device"
            std::string tableName = root["user"].asString() + "_device";
            std::string mac = get_mac();
            createDeviceTable(tableName);
            insertDeviceData(tableName, mac);
        }
        else if (request == "device") { // 查询用户设备列表
            std::string tableName = root["user"].asString() + "_device";
            queryDeviceList(conn, tableName);
        }
        else if (request == "query") {  // 查询指定设备某段时间采集的数据
            std::string mac = get_mac();
            std::string tableName = root["user"].asString() + "_" + mac + "_data";
            std::string date_start = root["start"].asString();
            std::string date_end = root["end"].asString();
            queryDeviceData(conn, tableName, date_start, date_end);
        }
    }
}

void SqlServer::createDeviceTable(const std::string& tableName)
{
    auto conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (conn->connect()) {
        bool res = conn->create_table("CREATE TABLE IF NOT EXISTS " + tableName +
                        "("
                        "mac         char(50)    NOT NULL,"
                        "PRIMARY KEY (mac)"
                        ") ENGINE = InnoDB");
        if (!res) { // 建表失败, 记录日志
            LOG_INFO << "fail to createDeviceTable " << tableName;
        }
    }
}

void SqlServer::createUserDataTable(const std::string& tableName)
{
    auto conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (conn->connect()) {
        bool res = conn->create_table("CREATE TABLE IF NOT EXISTS " + tableName +
                            "("
                            "date       datetime        NOT NULL,"
                            "temp       decimal(5, 2)   NOT NULL,"
                            "humi       decimal(5, 2)   NOT NULL"
                            ") ENGINE = InnoDB");
        if (!res) {
            LOG_INFO << "fail to createUserDataTable " << tableName;
        }
    }
}

// 插入设备id字段
void SqlServer::insertDeviceData(std::string tableName, std::string mac)
{
    auto conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (conn->connect()) {
        if (conn->lock_write(tableName))
        {
            conn->insert("INSERT IGNORE INTO " + tableName + "(mac)"
                        "VALUES('" + mac + "')");
            conn->unlock();
        }
        else 
        {
            LOG_INFO << "fail to lock write " << tableName;
        }
    }
}

// 插入一行数据到指定表中
void SqlServer::insertUserData(std::string tableName, std::string date, float temp, float humi)
{
    auto conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (conn->connect()) {
        if (conn->lock_write(tableName))
        {
            conn->insert("INSERT IGNORE INTO " + tableName + "(date, temp, humi)"
                        "VALUES('" + date + "'," + std::to_string(temp) + "," + std::to_string(humi) + ")");
            conn->unlock();
        }
        else 
        {
            LOG_INFO << "fail to lock write " << tableName;
        }
    }
}

void SqlServer::queryDeviceList(const muduo::net::TcpConnectionPtr& conn, const std::string& tableName) 
{
    auto sql_conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (sql_conn->connect()) {
        if (sql_conn->lock_read(tableName))
        {
            std::vector<std::string> field_names = sql_conn->query("SELECT mac FROM " + tableName);
            int row_count = static_cast<int>(field_names.size());
            if (!field_names.empty())   // 表不为空
            {
                std::vector<std::string> row;
                do {
                    row = sql_conn->next();
                    if (!row.empty())
                    {
                        Json::Value root;
                        root["reply"] = "device";
                        for (int i = 0; i < row_count; i++) {
                            root[field_names[i]] = row[i];
                        }
                        std::string line_data = root.toStyledString();
                        muduo::net::Buffer buf;
                        buf.append(line_data.data(), line_data.size());
                        int32_t len = static_cast<int32_t>(line_data.size());
                        int32_t be32 = muduo::net::sockets::hostToNetwork32(len);

                        buf.prepend(&be32, sizeof(be32));
                        conn->send(&buf);
                    }
                } while (!row.empty());
            }
            sql_conn->unlock();
        }
        else 
        {
            LOG_INFO << "fail to lock read" << tableName;
        }
    }
}

void SqlServer::queryDeviceData(const muduo::net::TcpConnectionPtr& conn, const std::string& tableName, const std::string& date_start, const std::string& date_end) 
{
    auto sql_conn = Connection_Pool<SqlConnection>::globalInstance()->getConnection();
    if (sql_conn->connect()) {
        if (sql_conn->lock_read(tableName))
        {
            std::vector<std::string> field_names = sql_conn->query("SELECT date, temp, humi FROM " + tableName +
                                                                   " WHERE Date(date) BETWEEN '" + date_start + "' AND "
                                                                   "'" + date_end + "' ORDER BY date");
            int row_count = static_cast<int>(field_names.size());
            if (!field_names.empty())   // 表不为空
            {
                std::vector<std::string> row;
                do {
                    row = sql_conn->next();
                    if (!row.empty())
                    {
                        Json::Value root;
                        root["reply"] = "data";
                        for (int i = 0; i < row_count; i++) {
                            root[field_names[i]] = row[i];
                        }
                        std::string line_data = root.toStyledString();
                        muduo::net::Buffer buf;
                        buf.append(line_data.data(), line_data.size());
                        int32_t len = static_cast<int32_t>(line_data.size());
                        int32_t be32 = muduo::net::sockets::hostToNetwork32(len);

                        buf.prepend(&be32, sizeof(be32));
                        conn->send(&buf);
                    }
                } while (!row.empty());
            }
            sql_conn->unlock();
        }
        else 
        {
            LOG_INFO << "fail to lock read" << tableName;
        }
    }
}
